IF OBJECT_ID('vw_projectpayment') is not null
 DROP VIEW vw_projectpayment;
GO

CREATE VIEW [dbo].[vw_projectpayment]
AS
SELECT     project.id, project.projectid, project.projecttitle AS title, project.costcenternumber, projecttype.projecttype AS projecttypedesc, projectstatus.statusname AS status, 
                      region.regionname AS region, country.countryname AS country, project.acronym, project.projectstartdate, project.projectenddate, partner.partnername, 
                      project.budgetoverride, project.statusoverride, ISNULL(project.managementfunds, 0) + ISNULL(project.partnergrantfunds, 0) AS totalfunds, 
                      (CASE WHEN project.parentprojectid IS NULL THEN ISNULL(project.managementfunds, 0) ELSE ISNULL(project.partnergrantfunds, 0) END) - ISNULL
                          ((SELECT     SUM(paymentrequestamount) AS Expr1
                              FROM         dbo.paymentrequestlineitem AS payment INNER JOIN
                                  dbo.paymentrequest AS paymentrequest ON paymentrequest.id = payment.paymentrequestid
                              WHERE     (projectid = project.id) AND (paymentrequest.statuscode != 'Cancelled' AND paymentrequest.statuscode != 'Cancel Requested')), 0) - ISNULL
                          ((SELECT DISTINCT TOTAL
                              FROM         dbo.solomon_all AS soloman
                              WHERE     ([CIPE GRANT NUMBER] = project.projectid)), 0) AS remainingfunds
FROM         dbo.project AS project LEFT OUTER JOIN
                      dbo.projecttype AS projecttype ON projecttype.id = project.projecttypeid LEFT OUTER JOIN
                      dbo.projectstatus AS projectstatus ON projectstatus.id = project.projectstatusid LEFT OUTER JOIN
                      dbo.region AS region ON region.id = project.regionid LEFT OUTER JOIN
                      dbo.country AS country ON country.id = project.countryid LEFT OUTER JOIN
                      dbo.partner AS partner ON partner.id = project.partnerid

GO